<?php
class Ticket extends CActiveRecord{
    public static function model($className = __CLASS__) {
        return parent::model ( $className );
    }

    // goi den bang can ket noi   
    public function tableName() {                  
        return 'ticket';
    }

    public function insertRow($user_id,$ngay_quay,$province_id,$total_xu,$kieu_danh){   
        $date = date("Y-m-d H:i:s",time());
        $last_id =0;
        $connect = Yii::app()->db;
        $sql =   "INSERT INTO ticket (user_id, ngay_quay, province_id, total_xu, kieu_danh, create_date) VALUES (:user_id, :ngay_quay, :province_id, :total_xu, :kieu_danh, :create_date)";
        $command = $connect->createCommand ($sql);
        $command->bindValue ( ":user_id", $user_id, PDO::PARAM_STR );
        $command->bindValue ( ":ngay_quay", $ngay_quay, PDO::PARAM_STR );  
        $command->bindValue ( ":province_id", $province_id, PDO::PARAM_STR ); 
        $command->bindValue ( ":total_xu", $total_xu, PDO::PARAM_STR ); 
        $command->bindValue ( ":kieu_danh", $kieu_danh, PDO::PARAM_STR );
        $command->bindValue ( ":create_date", $date, PDO::PARAM_STR );         
        $result = $command->execute();
        $last_id = Yii::app()->db->getLastInsertId();
        return $last_id;
    }

    public function getRowByUserAndDate($user_id,$ngay_quay){
        $cache = false;
        $row = array();
        $cache = Yii::app()->cache;
        if($cache != null){
            $cacheService = new CacheService("Ticket","getRowByUserAndDate",$user_id.$ngay_quay);
            $key = $cacheService->createKey();
            $dependency = $cacheService->createDependency();
            $cache = Yii::app ()->cache->get ( $key ); 
        }
        //$cache=false;
        if($cache == false){ 
            $sql =  "SELECT id, user_id, total_xu FROM ticket 
            WHERE user_id = ".$user_id." AND ngay_quay= '".$ngay_quay."'";
            $connect = Yii::app()->db;
            $command = $connect->createCommand($sql);
            $row = $command->queryRow();

            Yii::app ()->cache->set ( $key, $row, ConstantsUtil::TIME_CACHE_3600, new CGlobalStateCacheDependency ( $dependency ) );
        }else{
            $row = $cache;
        }
        return $row;
    }

    public function getAllRows(){
        $row = array();
        $cache = Yii::app()->cache;
        if($cache != null){
            $cacheService = new CacheService("Ticket","getAllRows","");
            $key = $cacheService->createKey();
            $dependency = $cacheService->createDependency();
            $cache = Yii::app ()->cache->get ( $key ); 
        }
        //$cache=false;
        if($cache == false){ 
            $sql = "SELECT ticket.id, user_veso.username, DATE_FORMAT(ticket.ngay_quay,'%d/%m/%Y') as ngay_quay_thuong, province.name as province_name, ticket.kieu_danh, ticket.total_xu
            FROM ticket left join user_veso ON ticket.user_id = user_veso.id 
            left join province ON ticket.province_id = province.id ORDER BY ticket.id DESC";

            $connect = Yii::app()->db;
            $command = $connect->createCommand($sql);
            $row = $command->queryAll();

            Yii::app ()->cache->set ( $key, $row, ConstantsUtil::TIME_CACHE_3600, new CGlobalStateCacheDependency ( $dependency ) );
        }else{
            $row = $cache;
        }
        return $row;    
    }

    public function getRowsByTicketPaging($begin,$end){
        // $cache = false;
        $row = array();
        $cache = Yii::app()->cache;
        if($cache != null){
            $cacheService = new CacheService("ticket","getRowsByTicketPaging",$begin.$end);
            $key = $cacheService->createKey();
            $dependency = $cacheService->createDependency();
            $cache = Yii::app ()->cache->get ( $key ); 
        }
        // $cache=false;
        if($cache == false){ 
            $sql = "SELECT ticket.id, user_veso.username, DATE_FORMAT(ticket.ngay_quay,'%d/%m/%Y') as ngay_quay_thuong, province.name as province_name, ticket.kieu_danh, ticket.total_xu
            FROM ticket left join user_veso ON ticket.user_id = user_veso.id 
            left join province ON ticket.province_id = province.id ORDER BY ticket.id DESC LIMIT ".$begin.",".$end."";            
            $connect = Yii::app()->db;
            $command = $connect->createCommand($sql);
            $row = $command->queryAll();

            Yii::app ()->cache->set ( $key, $row, ConstantsUtil::TIME_CACHE_3600, new CGlobalStateCacheDependency ( $dependency ) );
        }else{
            $row = $cache;
        }
        return $row;
    }

    public function getRowByDateAndBonus($date){
        $row = array();
        $cache = Yii::app()->cache;
        if($cache != null){
            $cacheService = new CacheService("Ticket","getRowByDateAndBonus",$date);
            $key = $cacheService->createKey();
            $dependency = $cacheService->createDependency();
            $cache = Yii::app ()->cache->get ( $key ); 
        }
        if($cache == false){ 
            $sql = "SELECT count(id) AS `count` FROM ticket WHERE ngay_quay ='".$date."' AND tien_thuong > 0";         
            $connect = Yii::app()->db;
            $command = $connect->createCommand($sql);
            $row = $command->queryRow();
            Yii::app ()->cache->set ( $key, $rows, ConstantsUtil::TIME_CACHE_3600, new CGlobalStateCacheDependency ( $dependency ) );

        }else{
            $rows = $cache;  
        } 
        return $row;   
    } 

    public function getRowByDateAndBonusPaging($date,$begin,$end){
        $row = array();
        $cache = Yii::app()->cache;
        if($cache != null){
            $cacheService = new CacheService("Ticket","getRowByDateAndBonusPaging",$date.$begin.$end);
            $key = $cacheService->createKey();
            $dependency = $cacheService->createDependency();
            $cache = Yii::app ()->cache->get ( $key ); 
        }
        if($cache == false){
            $sql = "SELECT id, user_id, ngay_quay, province_id, kieu_danh, tien_thuong FROM ticket 
            WHERE ngay_quay ='".$date."' AND tien_thuong > 0 ORDER BY tien_thuong DESC LIMIT ".$begin.",".$end;            
            $connect = Yii::app()->db;
            $command = $connect->createCommand($sql);
            $row = $command->queryAll();
            Yii::app ()->cache->set ( $key, $rows, ConstantsUtil::TIME_CACHE_3600, new CGlobalStateCacheDependency ( $dependency ) );

        }else{
            $rows = $cache;    
        }
        return $row;     
    }

    public function getRowByBonusAndUserId($user_id){
        $row = array();
        $cache = Yii::app()->cache;
        if($cache != null){
            $cacheService = new CacheService("Ticket","getRowByBonusAndUserId",$user_id);
            $key = $cacheService->createKey();
            $dependency = $cacheService->createDependency();
            $cache = Yii::app ()->cache->get ( $key ); 
        }
        if($cache == false){
            $sql = "SELECT id,user_id,ngay_quay,province_id,total_xu,kieu_danh,tien_thuong FROM ticket WHERE tien_thuong > 0 AND user_id=".$user_id." ORDER BY id DESC LIMIT 0,1";         
            $connect = Yii::app()->db;
            $command = $connect->createCommand($sql);
            $row = $command->queryAll();
            Yii::app ()->cache->set ( $key, $rows, ConstantsUtil::TIME_CACHE_3600, new CGlobalStateCacheDependency ( $dependency ) );

        }else{
            $rows = $cache;     
        } 
        return $row;   
    }

    public function countRowByDateUserIdAndBonus($user_id,$date_form,$date_to,$bonus){
        $row = array();
        $cache = Yii::app()->cache;
        if($cache != null){
            $cacheService = new CacheService("Ticket","countRowByDateUserIdAndBonus",$user_id.$date_form.$date_to.$bonus);
            $key = $cacheService->createKey();
            $dependency = $cacheService->createDependency();
            $cache = Yii::app ()->cache->get ( $key ); 
        }
        if($cache == false){
            if($bonus=="trungthuong"){
                $sql = "SELECT count(id) as count FROM ticket 
                WHERE ngay_quay >='".$date_form."' AND ngay_quay <='".$date_to."' AND tien_thuong > 0 AND user_id=".$user_id;     
            }else{
                $sql = "SELECT count(id) as count FROM ticket 
                WHERE ngay_quay >='".$date_form."' AND ngay_quay <='".$date_to."' AND user_id=".$user_id; 
            }               
            $connect = Yii::app()->db;
            $command = $connect->createCommand($sql);
            $row = $command->queryRow();
            $row = $command->queryAll();
            Yii::app ()->cache->set ( $key, $row, ConstantsUtil::TIME_CACHE_3600, new CGlobalStateCacheDependency ( $dependency ) );

        }else{
            $row = $cache;     
        }  
        return $row;         
    }

    public function getRowByDateUserIdAndBonusPaging($user_id,$date_form,$date_to,$bonus,$begin,$end){
        $row = array();
        $cache = Yii::app()->cache;
        if($cache != null){
            $cacheService = new CacheService("Ticket","getRowByDateUserIdAndBonusPaging",$user_id.$date_form.$date_to.$bonus.$begin.$end);
            $key = $cacheService->createKey();
            $dependency = $cacheService->createDependency();
            $cache = Yii::app ()->cache->get ( $key ); 
        }
        if($cache == false){
            if($bonus =="trungthuong"){ 
                $sql = "SELECT id, user_id, ngay_quay, province_id, total_xu, kieu_danh, tien_thuong, create_date FROM ticket 
                WHERE ngay_quay >='".$date_form."' AND ngay_quay <='".$date_to."' AND tien_thuong > 0 AND user_id=".$user_id." ORDER BY id DESC LIMIT ".$begin.",".$end;
            }else{
                $sql = "SELECT id, user_id, ngay_quay, province_id, total_xu, kieu_danh, tien_thuong, create_date FROM ticket 
                WHERE ngay_quay >='".$date_form."' AND ngay_quay <='".$date_to."' AND user_id=".$user_id." ORDER BY id DESC LIMIT ".$begin.",".$end;            
            }         

            $connect = Yii::app()->db;
            $command = $connect->createCommand($sql);
            $row = $command->queryAll(); 
            Yii::app ()->cache->set ( $key, $row, ConstantsUtil::TIME_CACHE_3600, new CGlobalStateCacheDependency ( $dependency ) );

        }else{
            $row = $cache;     
        }
        return $row;         
    }

    public function countRowInDate($fromDate,$toDate){
        $row = array();
        $cache = Yii::app()->cache;
        if($cache != null){
            $cacheService = new CacheService("Ticket","countRowInDate",$fromDate.$toDate);
            $key = $cacheService->createKey();
            $dependency = $cacheService->createDependency();
            $cache = Yii::app ()->cache->get ( $key ); 
        }
        if($cache == false){
            $str = "";
            $sql = "SELECT ngay_quay FROM ticket WHERE ngay_quay BETWEEN '$fromDate' AND '$toDate' AND tien_thuong > 0 ".$str." GROUP BY ngay_quay";

            $connect = Yii::app()->db;
            $command = $connect->createCommand($sql);
            $row = $command->queryAll(); 
            Yii::app ()->cache->set ( $key, $row, ConstantsUtil::TIME_CACHE_3600, new CGlobalStateCacheDependency ( $dependency ) );

        }else{
            $row = $cache;     
        }
        return $row; 
    }

    public function getRowInDatePaging($fromDate,$toDate,$begin,$end){
        $row = array();
        $cache = Yii::app()->cache;
        if($cache != null){
            $cacheService = new CacheService("Ticket","getRowInDatePaging",$fromDate.$toDate.$begin.$end);
            $key = $cacheService->createKey();
            $dependency = $cacheService->createDependency();
            $cache = Yii::app ()->cache->get ( $key ); 
        }
        if($cache == false){
            $str = "";
            $sql = "SELECT id, ngay_quay
            FROM ticket WHERE ngay_quay BETWEEN '$fromDate' AND '$toDate' AND tien_thuong > 0 ".$str." GROUP BY ngay_quay ORDER BY ngay_quay DESC LIMIT $begin,$end";
            $connect = Yii::app()->db;
            $command = $connect->createCommand($sql);
            $row = $command->queryAll(); 
            Yii::app ()->cache->set ( $key, $row, ConstantsUtil::TIME_CACHE_3600, new CGlobalStateCacheDependency ( $dependency ) );

        }else{
            $row = $cache;     
        }
        return $row; 
    }

    public function getRowByDate($date){
        $row = array();
        $cache = Yii::app()->cache;
        if($cache != null){
            $cacheService = new CacheService("Ticket","getRowByDate",$date);
            $key = $cacheService->createKey();
            $dependency = $cacheService->createDependency();
            $cache = Yii::app ()->cache->get ( $key ); 
        }
        if($cache == false){
            $str = "";
            $sql = "SELECT t1.id, t1.ngay_quay, t2.username 
            FROM ticket t1 LEFT JOIN user_veso t2 ON t1.user_id = t2.id WHERE t1.ngay_quay = '$date' AND t1.tien_thuong > 0 ORDER BY id DESC";
            //echo $sql;die;
            $connect = Yii::app()->db;
            $command = $connect->createCommand($sql);
            $row = $command->queryAll(); 
            Yii::app ()->cache->set ( $key, $row, ConstantsUtil::TIME_CACHE_3600, new CGlobalStateCacheDependency ( $dependency ) );

        }else{
            $row = $cache;     
        }
        return $row; 
    }

    public function getRowInArrDate($arr_date){
        $row = array();
        $cache = Yii::app()->cache;
        if($cache != null){
            $cacheService = new CacheService("Ticket","getRowInArrDate",str_replace(",",".",$arr_date));
            $key = $cacheService->createKey();
            $dependency = $cacheService->createDependency();
            $cache = Yii::app ()->cache->get ( $key ); 
        }
        if($cache == false){
            $str = "";
            $sql = "SELECT t1.id, t1.ngay_quay, t2.username 
            FROM ticket t1 LEFT JOIN user_veso t2 ON t1.user_id = t2.id WHERE t1.ngay_quay IN ($arr_date) AND t1.tien_thuong > 0 ORDER BY t1.ngay_quay DESC";
            //echo $sql;die;
            $connect = Yii::app()->db;
            $command = $connect->createCommand($sql);
            $row = $command->queryAll(); 
            Yii::app ()->cache->set ( $key, $row, ConstantsUtil::TIME_CACHE_3600, new CGlobalStateCacheDependency ( $dependency ) );

        }else{
            $row = $cache;     
        }
        return $row; 
    }   


}
